﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using UniversityStudentManagemnetSystem.DAO.DAL;

namespace UniversityStudentManagemnetSystem.DAO.Gateway
{
    public class ResultGateway:Gateway
    {
        public DataTable GetAllEnrollCoursesForRegNo(string regNo)
        {
            string selectQuery =
                string.Format(
                    "Select c.Code,c.Id from t_Course as c inner join t_StudentEnroll as s on c.Id=s.CourseId and s.EnrollFlag='Y' and s.RegNo='"+regNo+"'");

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet myDataset = new DataSet();
            sqlDataAdapter.Fill(myDataset, "CourseTable");
            DataTable myDataTable = myDataset.Tables["CourseTable"];
            return myDataTable;
        }

        public void SaveResult(Student aStudent)
        {
            try
            {

                Connection.Open();
                string insertQuery = string.Format("insert into t_ResultEntry values (@RegNo,@CourseId,@DepartmentId,@Email,@GradeId,@Name,@PublishResult)");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@RegNo", SqlDbType.VarChar);
                Command.Parameters["@RegNo"].Value = aStudent.RegNo;
                Command.Parameters.Add("@CourseId", SqlDbType.Int);
                Command.Parameters["@CourseId"].Value = aStudent.Course.CourseId;
                Command.Parameters.Add("@DepartmentId", SqlDbType.Int);
                Command.Parameters["@DepartmentId"].Value = aStudent.Department.DeptId;
                Command.Parameters.Add("@Email", SqlDbType.VarChar);
                Command.Parameters["@Email"].Value = aStudent.Email;
                Command.Parameters.Add("@GradeId", SqlDbType.Int);
                Command.Parameters["@GradeId"].Value = aStudent.GradeId;
                Command.Parameters.Add("@Name", SqlDbType.VarChar);
                Command.Parameters["@Name"].Value = aStudent.Name;
                Command.Parameters.Add("@PublishResult", SqlDbType.VarChar);
                Command.Parameters["@PublishResult"].Value ="Yes" ;
                Command.ExecuteNonQuery();

            }
            catch (Exception exception)
            {
                
                throw new Exception("Database Error");
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
        }

        public bool IsUploadResultForthisCourse(Student aStudent)
        {
            bool hasRows = false;
            try
            {
                Connection.Open();
                string insertQuery =
                    string.Format(
                        "select r.CourseId from t_Course as c inner join t_ResultEntry as r on c.Id= r.CourseId where RegNo='"+aStudent.RegNo+"' and r.CourseId='"+aStudent.Course.CourseId+"'");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@CourseId", SqlDbType.Int);
                Command.Parameters["@CourseId"].Value = aStudent.Course.CourseId;
                SqlDataReader reader = Command.ExecuteReader();
                if (reader!=null)
                {
                    hasRows = reader.HasRows;
                }

            }
            catch (Exception exception)
            {
                
                throw new Exception("DataBase Error",exception);
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return hasRows;
        }

        public Student SearchStudentResultByRegNo(string regNo)
        {
            Student student = new Student();
            try
            {

                Connection.Open();
                string selectQuery = string.Format("select s.Name,s.Email,s.DepartmentId,d.Name as Department from t_StudentRegistation as s inner join t_Department as d on s.DepartmentId=d.Id and s.RegNo='" + regNo + "'");
                Command.CommandText = selectQuery;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {

                    student.Name = reader["Name"].ToString();
                    student.Email = reader["Email"].ToString();
                    student.Department.DeptId = Convert.ToInt32(reader["DepartmentId"]);
                    student.Department.Name = reader["Department"].ToString();


                }
                reader.Close();


            }
            catch (Exception exception)
            {
                throw new Exception("Database Error",exception);

            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return student;
        }

        public DataTable GetAllEnrollCoursesForSelectedRegNo(string selectedRegNo)
        {
            DataTable table = null;
            string selectQuery = string.Format("select * from view_Result_2 where RegNo='" + selectedRegNo + "' ");
            Command.CommandText = selectQuery;
            Command.Parameters.Clear();
            queryString = GetQueryString(Command);
            try
            {                
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(queryString,GetConnectionString());
                DataSet myDataSet = new DataSet();
                sqlDataAdapter.Fill(myDataSet);
                table = myDataSet.Tables[0];
            }
            catch (Exception exception)
            {
                
                throw new Exception("System Error!",exception);
            }
            return table;
        }

        public DataTable GetAllCompletedCourseForSelectedRegNo(string selectedRegNo)
        {
            DataTable table = null;
            string selectQuery =
                string.Format(
                    "select * from view_Total_Cgpa_Grade where RegNo='" + selectedRegNo + "' and PublishResult='Yes'");
            Command.CommandText = selectQuery;
            Command.Parameters.Clear();
            queryString = GetQueryString(Command);
            try
            {
                SqlDataAdapter sqlDataAdapter=new SqlDataAdapter(queryString,GetConnectionString());
                DataSet myDataSet = new DataSet();
                sqlDataAdapter.Fill(myDataSet);
                table = myDataSet.Tables[0];
            }
            catch (Exception exception)
            {
                
                throw new Exception("System Error!",exception);
            }
            return table;
        }

        public string GetTotalCreditOfEnrolledCoursesForSelectedRegNo(string selectedRegNo)
        {
            int credit = 0;
            try
            {
                Connection.Open();
                string selectQuery = string.Format("select Sum(Credit) as Credit from view_Result_2 where RegNo='" + selectedRegNo + "'");
                Command.CommandText = selectQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@Credit", SqlDbType.Float);
                Command.Parameters["@Credit"].Value = credit;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    credit = Convert.ToInt32(reader["Credit"]);
                }
            }
            
            catch (Exception exception)
            {
                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return credit.ToString();
        }

        public string GetAllCompletedCreditForSelectedRegNo(string selectedRegNo)
        {
            int completedCredit = 0;
            try
            {
                Connection.Open();
                string selectQuery =
                    string.Format(
                        "select Sum(Credit) as Credit from view_Result_2 where RegNo='" + selectedRegNo + "' and PublishResult='Yes'");
                Command.CommandText = selectQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@Credit", SqlDbType.Float);
                Command.Parameters["@Credit"].Value = completedCredit;
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    completedCredit = Convert.ToInt32(sqlDataReader["Credit"]);
                }
            }
            catch (Exception exception)
            {
                
                throw new Exception("System Error!",exception);
            }
            finally
            {
                if(Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return completedCredit.ToString();
        }

        public string GetCgpaForSelectedRegNo(string selectedRegNo)
        {
            double cgpa = 0;
            try
            {
                Connection.Open();
                string selectQuery =
                    string.Format(
                        "select sum(GradePoint) as GradePoint from view_Total_Cgpa_Grade where RegNo='" + selectedRegNo + "' and PublishResult='Yes' ");
                Command.CommandText = selectQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@GradePoint", SqlDbType.Float);
                Command.Parameters["@GradePoint"].Value = cgpa;
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    cgpa = Convert.ToInt32(sqlDataReader["GradePoint"]);
                }
            }
            catch (Exception exception)
            {

                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return cgpa.ToString();
        }
    }
}